/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sbmart.smartserver.task;

import com.mchange.v2.c3p0.C3P0Registry;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.concurrent.ExecutorService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sbmart.object.*;
import sbmart.smartserver.cache.ConnectionManager;
import sbmart.smartserver.job.ServerTrsJob;
import sbmart.smartserver.service.ClientMstService;
import sbmart.smartserver.service.ClientTrsService;
import sbmart.smartserver.service.ServerProcedures;
import sbmart.util.Ping;

/**
 *
 * @author albert
 *
 */
public class ServerTrsTask implements Runnable {

    private CallableStatement call_get_trs_kirim_mst;
    private CallableStatement call_get_trs_kirim_detail;
    private CallableStatement call_get_trs_kirimp_mst;
    private CallableStatement call_get_trs_kirimp_detail;
    private CallableStatement call_get_trs_rent_terrace;
    private CallableStatement call_put_trs_sendhistory;
    private List<Header> headers = new ArrayList<Header>();
    private ExecutorService exec;
    public static final Logger LOG = LoggerFactory.getLogger("ServerTrs");

    public ServerTrsTask(List<Header> headers, ExecutorService exec) {
        this.headers = headers;
        this.exec = exec;
    }

    private Connection getServerConn() throws SQLException {
	 LOG.debug("[CALL]");
        Connection conn = C3P0Registry.pooledDataSourceByName("sbmart1").getConnection();
        conn.setAutoCommit(false);
        return conn;
    }

    private Connection getClientConn(Header header) throws SQLException {
        LOG.debug("[CALL]");
        Connection conn = ConnectionManager.instance().getClientConnection(header);
        return conn;
    }

    private void closeClientConn(Header header, Connection conn) throws SQLException {
        LOG.debug("[CALL]");
        ConnectionManager.instance().closeClientConnection(header, conn);
    }

    private void rollback(Header header, Connection serverConn, Connection clientConn, SQLException ex) {
        LOG.error("<ERROR> "+ header.getBranchId() +"|" + header.getBranchName() + "|" + header.getIpaddress(), ex);
        try {
            if (clientConn != null) {
                clientConn.rollback();
            }
            if (serverConn != null) {
                serverConn.rollback();
            }
            LOG.debug("[ROLLBACK] ", ex);
        } catch (SQLException ex1) {
            LOG.error("[ERROR]", ex1);
        } finally {
            return;
        }
    }

    @Override
    public void run() {
        LOG.info("[RUN] " + getClass().getSimpleName());
        Connection serverConn = null, clientConn = null;
        try {
            serverConn = getServerConn();
            if (!serverConn.isClosed()) {
                LOG.debug("[CALL]");
                for (Header header : headers) {
                    putTrsSend(header, serverConn);
                }
                serverConn.commit();
                LOG.debug("[COMMIT]");
            }
        } catch (SQLException ex) {
            LOG.error("[ERROR] ", ex);
        } catch (Exception e) {
            LOG.error("[ERROR] ", e);
        } finally {
            try {
                if (serverConn != null) {
                    serverConn.close();
                }
            } catch (SQLException ex) {
                LOG.error("[ERROR] ", ex);
            }
        }

        for (Header header : headers) {
            boolean isContinue = false;
	     boolean isSend = false;

            LOG.debug(header.getBranchId() + "|" + header.getBranchName() + "|" + header.getIpaddress());
            ServerTrsJob.count = 0;
            try {

                serverConn = getServerConn();
                if (serverConn.isClosed()) {
                    LOG.error("[ERROR] connection refuse by server");
                    isContinue = true;
                }		  

                if (isTrsSend(header, serverConn)) {
                    if (Ping.isReachable(header.getIpaddress())) {
                        isSend = true;
                        clientConn = getClientConn(header);
                        if (clientConn == null || clientConn.isClosed()) {
                            LOG.debug("[PASS] InUsed:" + header.getBranchName() + "|" + header.getIpaddress());
                            isContinue = true;
                        }

                        if (!isContinue) {
                            if (serverConn.isClosed()) {
                                serverConn = getServerConn();
                            }

                            if (!serverConn.isClosed() && !clientConn.isClosed()) {
                                getTrsKirim(header, serverConn, clientConn);        // Pointcast to gerai id
                                getTrsKirimP(header, serverConn, clientConn);       // Pointcast to gerai id
                                getTrsRentTerrace(header, serverConn, clientConn);  // Pointcast to gerai id
                                //getMstPkmGerai(header, clientConn, serverConn);
                                
                                getTrMutasiDest(header, serverConn, clientConn);      // Pointcast
                                getTrMutasiSrc(header, serverConn, clientConn);      // Pointcast
                            } else {
                                LOG.error("[ERROR] connection refuse or close");
                            }
                        }
                    }else{
                        LOG.error("[ERROR] PING " + header.getBranchName() + "|" + header.getIpaddress());
                    }
                }

            } catch (SQLException ex) {
                LOG.debug("[ERROR] " + header.getBranchName() + "|" + header.getIpaddress()+ " :\n" + ex.getMessage());
                try {
                    if (clientConn != null) {
                        clientConn.rollback();
                    }
                    if (serverConn != null) {
                        serverConn.rollback();
                    }
                    LOG.debug("[ROLLBACK] ", ex);
                } catch (SQLException ex1) {
                    LOG.error("[ERROR]", ex1);
                }
            } catch (Exception e) {
                LOG.debug("[ERROR] " + header.getBranchName() + "|" + header.getIpaddress(), e);
            } finally {
                try {
		      if(isSend){	                    
                        closeClientConn(header, clientConn);
                    }

                    if (serverConn != null) {
                        serverConn.close();
                    }
                } catch (SQLException ex) {
                    LOG.error("[ERROR] : "+ ex.getMessage());
                }

            }
        }
        LOG.info("[END] " + getClass().getSimpleName());
    }

    private boolean isTrsSend(Header header, Connection serverConn) {
//        LOG.debug("[CALL]");
        boolean isExists = true;
        try {
            CallableStatement call_is_trs_sendhistory_exists = serverConn.prepareCall(ServerProcedures.is_trs_sendhistory_exists);
            call_is_trs_sendhistory_exists.setInt("branch_id", header.getBranchId());
            call_is_trs_sendhistory_exists.registerOutParameter(1, Types.INTEGER);
            
            call_is_trs_sendhistory_exists.execute();
            int out = call_is_trs_sendhistory_exists.getInt(1);
            if (out == 0) {
                isExists = false;
                LOG.debug("<FALSE>");
            } else {
                isExists = true;
                LOG.debug("<TRUE>");
            }
        } catch (SQLException ex) {
            LOG.error("<ERROR>", ex);
        }
        return isExists;
    }

    private void putTrsSend(Header header, Connection serverConn) throws SQLException {
//        LOG.debug("[CALL] id: " + header.getBranchId());
        call_put_trs_sendhistory = serverConn.prepareCall(ServerProcedures.put_trs_sendhistory);
        call_put_trs_sendhistory.setInt(1, header.getBranchId());
        
        call_put_trs_sendhistory.execute();
    }

    private void getTrsKirim(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            call_get_trs_kirim_mst = serverConn.prepareCall(ServerProcedures.get_trs_kirim_mst);
            call_get_trs_kirim_detail = serverConn.prepareCall(ServerProcedures.get_trs_kirim_detail);

            call_get_trs_kirim_mst.setInt(1, header.getBranchId());
            ResultSet rs = call_get_trs_kirim_mst.executeQuery();
            List<TrsKirimMst> list = new ArrayList<TrsKirimMst>();
            while (rs.next()) {
                TrsKirimMst mst = new TrsKirimMst();

                mst.setTransId(rs.getInt("kirim_id"));
                mst.setTransNum(rs.getString("trans_num"));

                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                mst.setTransDate(cal1);

                mst.setBranchId(rs.getInt("branch_dest"));
                mst.setNopol(rs.getString("nopol"));
                mst.setOrderNum(rs.getString("order_num"));
                mst.setSubtotal(rs.getFloat("subtotal"));
                mst.setTax(rs.getFloat("tax"));
                mst.setTotal(rs.getFloat("total"));
                mst.setPbId(rs.getInt("pb_id"));
                mst.setUserId(rs.getString("users_id"));
                mst.setSerialKey(rs.getString("serial_keys"));
                mst.setPassKey(rs.getString("pass_keys"));
                mst.setPbNum(rs.getString("pb_num"));

                List<TrsKirimDetail> details = new ArrayList<TrsKirimDetail>();
                call_get_trs_kirim_detail.setInt(1, mst.getTransId());
                
                ResultSet rs2 = call_get_trs_kirim_detail.executeQuery();
                while (rs2.next()) {
                    TrsKirimDetail detail = new TrsKirimDetail();
                    detail.setItemId(rs2.getInt(1));
                    detail.setQtyOrder(rs2.getInt(2));
                    detail.setQuantity(rs2.getInt(3));
                    detail.setPrice(rs2.getFloat(4));
                    detail.setTax(rs2.getFloat(5));
                    detail.setAmount(rs2.getFloat(6));
                    detail.setIsTax(rs2.getByte(7));
                    detail.setPackaging(rs2.getString(8));
                    details.add(detail);
                }
                mst.setDetails(details);
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientTrsService.instance().putTrsKirim(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsKirimP(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            call_get_trs_kirimp_mst = serverConn.prepareCall(ServerProcedures.get_trs_kirimp_mst);
            call_get_trs_kirimp_detail = serverConn.prepareCall(ServerProcedures.get_trs_kirimp_detail);

            call_get_trs_kirimp_mst.setInt(1, header.getBranchId());
            
            ResultSet rs = call_get_trs_kirimp_mst.executeQuery();
            List<TrsKirimpMst> list = new ArrayList<TrsKirimpMst>();
            while (rs.next()) {
                TrsKirimpMst mst = new TrsKirimpMst();

                mst.setTransId(rs.getInt("kirimp_id"));
                mst.setTransNum(rs.getString("trans_num"));
                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                mst.setTransDate(cal1);

                mst.setBranchId(rs.getInt("branch_dest"));
                mst.setNopol(rs.getString("nopol"));
                mst.setOrderNum(rs.getString("order_num"));
                mst.setSubtotal(rs.getFloat("subtotal"));
                mst.setTax(rs.getFloat("tax"));
                mst.setTotal(rs.getFloat("total"));
                mst.setPbId(rs.getInt("pb_id"));
                mst.setUserId(rs.getString("users_id"));
                mst.setSerialKey(rs.getString("serial_keys"));
                mst.setPassKey(rs.getString("pass_keys"));
                mst.setTransType(rs.getByte("trans_type"));
                mst.setPbNum(rs.getString("pb_num"));

                List<TrsKirimpDetail> details = new ArrayList<TrsKirimpDetail>();
                call_get_trs_kirimp_detail.setInt(1, mst.getTransId());
                
                ResultSet rs2 = call_get_trs_kirimp_detail.executeQuery();
                while (rs2.next()) {
                    TrsKirimpDetail detail = new TrsKirimpDetail();
                    detail.setItemId(rs2.getInt(1));
                    detail.setQtyOrder(rs2.getInt(2));
                    detail.setQuantity(rs2.getInt(3));
                    detail.setPrice(rs2.getFloat(4));
                    detail.setTax(rs2.getFloat(5));
                    detail.setAmount(rs2.getFloat(6));
                    detail.setIsTax(rs2.getByte(7));
                    detail.setPackaging(rs2.getString(8));
                    details.add(detail);
                }
                mst.setDetails(details);
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientTrsService.instance().putTrsKirimP(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrsRentTerrace(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            call_get_trs_rent_terrace = serverConn.prepareCall(ServerProcedures.get_trs_rent_terrace);

            call_get_trs_rent_terrace.setInt("branch_id", header.getBranchId());
            
            ResultSet rs = call_get_trs_rent_terrace.executeQuery();
            List<TrsRentTerrace> list = new ArrayList<TrsRentTerrace>();
            while (rs.next()) {

                TrsRentTerrace trs = new TrsRentTerrace();
                trs.setRentId(rs.getInt("rent_id"));
                trs.setTransNum("trans_num");
                Calendar cal1 = null;
                if (rs.getDate("trans_date") != null) {
                    cal1 = Calendar.getInstance();
                    cal1.setTimeInMillis(rs.getDate("trans_date").getTime());
                }
                trs.setTransDate(cal1);
                trs.setBranchId(rs.getShort("branch_id"));
                trs.setRelationId(rs.getShort("relation_id"));
                trs.setArea(rs.getString("area"));
                trs.setLocation(rs.getByte("location"));
                trs.setCart(rs.getByte("cart"));
                trs.setElectricQuota(rs.getInt("electric_quota"));
                trs.setConduit(rs.getByte("conduit"));

                Calendar cal2 = null;
                if (rs.getDate("start_date") != null) {
                    cal2 = Calendar.getInstance();
                    cal2.setTimeInMillis(rs.getDate("start_date").getTime());
                }
                trs.setStartDate(cal2);

                Calendar cal3 = null;
                if (rs.getDate("end_date") != null) {
                    cal3 = Calendar.getInstance();
                    cal3.setTimeInMillis(rs.getDate("end_date").getTime());
                }
                trs.setEndDate(cal3);
                trs.setTermin(rs.getByte("termin"));
                trs.setPrice(rs.getFloat("price"));
                trs.setPayType(rs.getByte("pay_type"));
                trs.setProduct(rs.getString("product"));
                trs.setCartPrice(rs.getFloat("cart_price"));
                trs.setSubtotal(rs.getFloat("cart_price"));
                trs.setPpn(rs.getFloat("ppn"));
                trs.setTotal(rs.getFloat("total"));
                trs.setCartType(rs.getShort("cart_type"));
                trs.setProduct2(rs.getString("product2"));
                trs.setDimensi(rs.getByte("dimensi"));
                trs.setNotes(rs.getString("notes"));
                trs.setFirstParty(rs.getString("first_party"));
                trs.setInsertLog(rs.getString("insert_log"));
                trs.setEditLog(rs.getString("edit_log"));
                trs.setFirstPartyAddress(rs.getString("first_party_address"));
                trs.setLeadTime(rs.getShort("lead_time"));

                list.add(trs);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientTrsService.instance().putTrsRentTerrace(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrMutasiDest(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_tr_mutasi = serverConn.prepareCall(ServerProcedures.get_tr_mutasi_dest);
            call_get_tr_mutasi.setInt("dest_branch", header.getBranchId());
            
            ResultSet rs = call_get_tr_mutasi.executeQuery();
            List<TrMutasi> list = new ArrayList<TrMutasi>();
            while (rs.next()) {
                TrMutasi mst = new TrMutasi();
                mst.setNik(rs.getString("nik"));
                mst.setNama(rs.getString("nama"));
                mst.setAlamat(rs.getString("alamat"));
                mst.setTelp(rs.getString("telp"));
                mst.setDestStkary(rs.getString("dest_stkary"));
                mst.setAccept("accept");
                mst.setAcceptor("acceptor");
                mst.setPosisi(rs.getString("posisi"));

                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientTrsService.instance().putTrMutasiDest(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }

    private void getTrMutasiSrc(Header header, Connection serverConn, Connection clientConn) {
//        LOG.debug("[CALL]");
        try {
            CallableStatement call_get_tr_mutasi = serverConn.prepareCall(ServerProcedures.get_tr_mutasi_src);
            call_get_tr_mutasi.setInt("src_branch", header.getBranchId());
            
            ResultSet rs = call_get_tr_mutasi.executeQuery();
            List<TrMutasi> list = new ArrayList<TrMutasi>();
            while (rs.next()) {
                TrMutasi mst = new TrMutasi();
                mst.setNik(rs.getString("nik"));
                mst.setSrcStkary(rs.getString("src_stkary"));
                list.add(mst);
            }
            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                ClientTrsService.instance().putTrMutasiSrc(header, list, clientConn);
                clientConn.commit();
                LOG.debug("<COMMIT>");
            }
            serverConn.commit();

        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    }
    
    private void getMstPkmGerai(Header header, Connection serverConn, Connection clientConn) {
        try {
            CallableStatement call_get_mst_pkm_gerai = clientConn.prepareCall(ServerProcedures.get_mst_pkm_gerai);
            call_get_mst_pkm_gerai.setInt("branch_id", header.getBranchId());
            
            ResultSet rs = call_get_mst_pkm_gerai.executeQuery();
            List<MstPkmGerai> list = new ArrayList<MstPkmGerai>();
            while (rs.next()) {
                MstPkmGerai pkm = new MstPkmGerai();
                pkm.setBranchId(rs.getInt("branch_id"));
                pkm.setItemId(rs.getInt("item_id"));
                pkm.setPkm(rs.getFloat("pkm"));
                pkm.setDsi(rs.getFloat("dsi"));

                list.add(pkm);
            }

            if (!list.isEmpty()) {
                LOG.debug("<GET>");
                /* Put into mst_item_display */
                ClientMstService.instance().putMstPkmGerai(header, list, clientConn);
                serverConn.commit();
                LOG.debug("<COMMIT> : " + header.getBranchId() + ". " + header.getBranchName());
            }
            clientConn.commit();
        } catch (SQLException ex) {
            rollback(header, serverConn, clientConn, ex);
        }
    } 
    
}
